package com.basis.utils;

import com.basis.transform.MapResult;
import com.basis.transform.PostgresqlTransformer;
import com.mes.data.EmesParameter;
import org.hibernate.Query;
import org.hibernate.jdbc.Work;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@SuppressWarnings("unchecked")
public class HibernateDao {
	/**
	 * 执行sql更新语句
	 */
	public static int executeSql(String sql, Object... objs) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		return query.executeUpdate();
	}
	/**
	 * 执行sql更新语句
	 */
	public static int executeSql(String sql, Map params) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		return query.executeUpdate();
	}
	/**
	 * sql查询
	 * 返回Map，key为大写
	 */
	public static List<Map<String, Object>> query(String sql, Object... objs) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * 通过sql语句查询结果
	 */
	public static List<Map<String, Object>> query(String sql, Map params) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		//讲查询结果转化为Map结果
		query.setResultTransformer(PostgresqlTransformer.TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * 通过sql语句查询结果
	 */
	public static Map<String, Object> queryUnique(String sql, Object... objs) {
		List<Map<String, Object>> list = query(sql, objs);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 通过sql语句查询结果
	 */
	public static Map<String, Object> queryUnique(String sql, Map params) {
		List<Map<String, Object>> list = query(sql, params);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 获得查询语句的总记录数
	 */
	public static Long countSql(String sql, Map params) {
		String sql1 = sql;
		String upSql = sql.toUpperCase();
		int order = upSql.lastIndexOf(" ORDER ");
		if (order > sql.lastIndexOf(")")) {
			sql1 = sql1.substring(0, order);
		}
		return getSqlCount("select count(*) from (" + sql1 + ") tb", params);
	}

	/**
	 * 获得sql的统计结果
	 */
	public static Long getSqlCount(String sql, Object... objs) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (objs != null && objs.length > 0) {
			for (int i = 0; i < objs.length; i++) {
				query.setParameter(i, objs[i]);
			}
		}
		Object value = query.uniqueResult();
		if (value != null)
			return Long.parseLong(value.toString());
		return null;
	}
	/**
	 * 获得sql的统计结果
	 */
	public static Long getSqlCount(String sql, Map params) {
		Query query = getDao().getSession().createSQLQuery(sql);
		if (params != null && !params.isEmpty()) {
			for (Object key : params.keySet()) {
				query.setParameter((String)key, params.get(key));
			}
		}
		Object value = query.uniqueResult();
		if (value != null)
			return Long.parseLong(value.toString());
		return null;
	}
	/**
	 * 执行存储过程函数
	 * @param sql
	 * @param parms
	 */
	public static void call(final String sql, final Object...parms) {
		getDao().getSession().doWork(new Work() {
			public void execute(Connection connection) throws SQLException {
				CallableStatement ps = null;
				try {
					ps = connection.prepareCall(sql);
					if (parms != null) {
						for (int i = 0; i < parms.length; i++) {
							ps.setObject(i + 1, parms[i]);
						}
					}
					ps.execute();
				} finally {
					try {
						ps.close();
					} catch (Exception e) {
					}
				}
			}
		});
	}
	
	/**
	 * 得到表字段属性
	 * 包括：
	 * name：列名
	 * isNotNull：字段是否能为空
	 * autoAdd：是否自动增长
	 * length：字段长度
	 * text：字段说明
	 * type：字段在数据库的类型名称
	 * signed：
	 * readOnly：只读
	 */
	public static List<Map<String, Object>> getFields(final String table) {
		final List list = new ArrayList();
		getDao().getSession().doWork(new Work() {
			public void execute(Connection connection) {
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					String sql = null;
					if (table.toUpperCase().indexOf("SELECT") > -1) {
						sql = table;
					} else {
						sql = "select * from " + table + " where 1=2";
					}
					ps = connection.prepareStatement(sql);
					rs = ps.executeQuery();
					Map map = null;
					ResultSetMetaData rsmd = ps.getMetaData();
					int size = rsmd.getColumnCount();
					String name = null;
					
					for (int i = 1; i <= size; i++) {
						try {
							map = new MapResult();
							map.put("name", rsmd.getColumnName(i));
							name = rsmd.getSchemaName(i);
							map.put("text", name == null || "".equals(name) ? rsmd.getColumnLabel(i) : name);
							map.put("type", rsmd.getColumnTypeName(i));
							map.put("length", rsmd.getColumnDisplaySize(i));
							map.put("isNotNull", rsmd.isNullable(i));
							map.put("fixed", rsmd.getScale(i));//保留小数点位数
							map.put("autoAdd", rsmd.isAutoIncrement(i));
							map.put("readOnly", rsmd.isReadOnly(i));
							map.put("signed", rsmd.isSigned(i));
							list.add(map);
						} catch (Exception e) {
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						rs.close();
					} catch (Exception e) {
					}
					try {
						ps.close();
					} catch (Exception e) {
					}
				}
			}
		});
		return list;
	}

	/**
	 * 获取系统参数值
	 * @return
	 */
	public static String getParameterValue(String code) {
		OBQuery<EmesParameter> query = getDao().createQuery(EmesParameter.class, "code = :code");
		query.setNamedParameter("code", code);
		EmesParameter param =  query.uniqueResult();
		if (param == null) {
			throw new RuntimeException("未搜索到编码[" + code + "]对应的系统参数！");
		}
		return param.getValue();
	}

	private static OBDal getDao() {
		return OBDal.getInstance();
	}
}